-- With the LIMIT option, the programer has a control over the amount of the processed rows in one step!.
-- Courtesy of Laszlo Czinkoczki


drop table newemp;
create table newemp as select * from hr.employees where 1>2;

CREATE OR REPLACE PROCEDURE FETCH_LIMIT
  (p_tname VARCHAR2, p_rows NUMBER DEFAULT 10) 
IS
  type emp_cur_type IS REF CURSOR;  
  c1 emp_cur_type; j number:=0;
  type c_type is table of  employees%rowtype;
  emp c_type; 
  stmt varchar2(200);  
BEGIN
  stmt:='SELECT * FROM '||p_tname;
  OPEN c1 FOR stmt;
  LOOP
    FETCH c1 BULK COLLECT INTO emp LIMIT p_rows;
    EXIT WHEN c1%NOTFOUND and emp.count=0;
    j:=j+1;    
    dbms_output.put_line('NUMBER OF LOOPS:'||j);
    FORALL I IN emp.FIRST..emp.LAST
      INSERT INTO NEWEMP VALUES emp(I);
  END LOOP;
  FORALL I IN emp.FIRST..emp.LAST
     INSERT INTO NEWEMP VALUES emp(I);
  CLOSE c1;
END FETCH_LIMIT;
/
